package com.xiguaji.tiktok.v1.tiktok.aweme.repository;

import com.xiguaji.tiktok.v1.tiktok.aweme.dto.AwemeMentionResponse;
import com.xiguaji.tiktok.v1.tiktok.aweme.model.AwemeMention;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface AwemeMentionRepository extends CrudRepository<AwemeMention,Long> {

    @Query("select " +
            "REPLACE(REPLACE (REPLACE (m.mention_avatar, 'sign-sg', 'amd-va'), 'sign-va', 'amd-va'),'webp','jpeg') avatar,m.mention_nickname nickname,m.mention_unique_id unique_id,count(*) mention," +
            "m.mention_enterprise_verify_reason enterprise_verify_reason,m.mention_custom_verify custom_verify," +
            "avg(a.o_play_count) avg_view,sum(a.o_digg_count+o_share_count+o_comment_count)/sum(a.o_play_count)*100 engagement_rate," +
            "(avg( a.o_play_count ) - b.o_avg_views)/ b.o_avg_views*100 performance  " +
            "from aweme_mention m " +
            "left join aweme a on m.aweme_id = a.o_aweme_id " +
            "left join blogger_main b ON a.o_uid = b.o_uid " +
            "where m.uid = :uid and " +
            "m.aweme_create_time > :startTime " +
            "group by m.mention_unique_id " +
            "order by mention desc " +
            "limit 0,:size")
    List<AwemeMentionResponse> getTopByUidAndAwemeCreateTimeLessThanOrderByAvgView(long uid, long startTime, int size);


    @Query("select " +
            "REPLACE(REPLACE (REPLACE (m.mention_avatar, 'sign-sg', 'amd-va'), 'sign-va', 'amd-va'),'webp','jpeg') avatar,m.mention_nickname nickname,m.mention_unique_id unique_id,count(*) mention," +
            "m.mention_enterprise_verify_reason enterprise_verify_reason,m.mention_custom_verify custom_verify," +
            "avg(a.o_play_count) avg_view,sum(a.o_digg_count+o_share_count+o_comment_count)/sum(a.o_play_count)*100 engagement_rate," +
            "(avg( a.o_play_count ) - b.o_avg_views)/ b.o_avg_views*100 performance  " +
            "from aweme_mention m " +
            "left join aweme a on m.aweme_id = a.o_aweme_id " +
            "left join blogger_main b ON a.o_uid = b.o_uid " +
            "where m.uid = :uid and " +
            "m.aweme_create_time > :startTime and " +
            "ISNULL(m.mention_enterprise_verify_reason) = 0 and " +
            "LENGTH(m.mention_enterprise_verify_reason) > 0 " +
            "group by m.mention_unique_id " +
            "order by mention desc " +
            "limit 0,:size")
    List<AwemeMentionResponse> getEnterpriseTopByUidAndAwemeCreateTimeLessThanOrderByAvgView(long uid, long startTime, int size);
}
